package com.movieproject.daos.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.movieproject.daos.QueueDAO;
import com.movieproject.dataobjects.Movie;
import com.movieproject.dataobjects.Queue;
import com.movieproject.dataobjects.QueueRecord;
import com.movieproject.dataobjects.User;

@Service
public class QueueDAOImp implements QueueDAO {
   
   private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void insert(Queue queue) {
		String sql = "INSERT INTO QUEUE" + "(MovieID, UserID, Position) VALUES (?,?,?)";

		Connection conn = null;

		try {
			conn = dataSource.getConnection();
			PreparedStatement pss = conn.prepareStatement(sql);
			pss.setInt(1, queue.getMovieID());
			pss.setInt(2, queue.getUserID());
			pss.setInt(3, queue.getPosition());
			pss.executeUpdate();
			pss.close();

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
	}
   
    @Autowired
    private SessionFactory sessionFactory;
    
    @Override
    public Map<User, List<Movie>> getAllUserQueues() {
	Query userquery = sessionFactory.getCurrentSession().createQuery("from User"); //get all users
	List<?> l = userquery.list();
	Map<User, List<Movie>> userQueues = new HashMap<User, List<Movie>>();
	for(Object user : l) {
	    Query queuequery = sessionFactory.getCurrentSession().createQuery("from QueueRecord where UserID = :u"); //get queue for specific user
	    queuequery.setParameter("u", ((User) user).getUserID());
	    List<?> lp = queuequery.list();
	    ArrayList<Movie> queue = new ArrayList<Movie>();
	    for(Object movie : lp) queue.add((Movie) movie);
	    userQueues.put((User) user, queue);
	}
	return userQueues;
    }
    
    @Override
    public List<QueueRecord> getAllQueues()
    {
    	Query query = sessionFactory.getCurrentSession().createQuery("from QueueRecord");
    	List<?> l = query.list();
    	ArrayList<QueueRecord> queueList = new ArrayList<QueueRecord>();
    	for(Object o : l)
    	{
    		queueList.add((QueueRecord) o);    		
    	}
    	return queueList;
    }

    @Override
    public List<Movie> getUserQueue(User user) {
	String querystring = "from QueueRecord where UserID = :q order by Position asc";
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("q", user.getUserID());
	List<?> l = query.list(); // query returns a raw list. 
	ArrayList<Movie> queue = new ArrayList<Movie>();
	for(Object o : l) {
	    Movie movie = ((QueueRecord) o).getMovie();
	    queue.add(movie);
	}
	return queue;
    }
    
//    @Override
//    public List<Movie> getUserQueueByUserId(int userId) {
//	String querystring = "from QueueRecord q where UserID = :q order by Position asc";
//	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
//	query.setParameter("q", userId);
//	List<?> l = query.list(); // query returns a raw list. 
//	ArrayList<Movie> queue = new ArrayList<Movie>();
//	for(Object o : l) {
//	    Movie movie = ((QueueRecord) o).getMovie();
//	    queue.add(movie);
//	}
//	return queue;
//    }

    @Override
    public void updateUserQueue(User user, List<Movie> queue) {
	deleteUserQueue(user);
	for(int i = 0; i < queue.size(); i++) {
	    QueueRecord queueRecord = new QueueRecord(queue.get(i), user, i + 1); 
	    sessionFactory.getCurrentSession().merge(queueRecord);
	}
    }

    @Override
    public void deleteUserQueue(User user) {
	String querystring = "delete QueueRecord where UserID = :u"; 
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	query.executeUpdate();
    }

    @Override
    public void addMovie(User user, Movie movie) {
	String querystring = "select max(position) from QueueRecord where UserID = :u"; //get position of last movie in queue
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	List<?> l = query.list();
	Integer maxposition;
	if(!l.isEmpty()) {
	    maxposition = (Integer) l.get(0);
	}
	else
	    maxposition = 0;
	QueueRecord queueRecord = new QueueRecord(movie, user, maxposition + 1); //add movie to queue
	sessionFactory.getCurrentSession().merge(queueRecord); 
    }

    @Override
    public Movie peekMovie(User user) {
	String querystring = "from QueueRecord where UserID = :u and Position = 1";
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	List<?> l = query.list();
	if(!l.isEmpty()) {
	    QueueRecord q = (QueueRecord) l.get(0);
	    return q.getMovie();
	}
	else return null;
    }

    @Override //this method does not work
    public Movie peekInStockMovie(User user) {
	String querystring = "select q from QueueRecord q join q.movie join q.movie.inventoryItems where UserID = :u and inStock = 1 order by Position asc";
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	query.setMaxResults(1);
	List<?> l = query.list();
	if(!l.isEmpty()) {
	    QueueRecord q = (QueueRecord) l.get(0);
	    return q.getMovie();
	}
	else return null;
    }

    @Override
    public Movie pollMovie(User user) {
	String querystring = "from QueueRecord where UserID = :u order by Position asc";  // get user queuerecords
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	List<?> l = query.list();
	if(l.isEmpty()) return null;
	for(int i = 1; i < l.size(); i++) {//decrement Positions of all but the first records in queue
	    QueueRecord queueRecord = (QueueRecord) l.get(i);
	    queueRecord.setPosition(queueRecord.getPosition() - 1);
	    sessionFactory.getCurrentSession().merge(queueRecord);
	}
	QueueRecord queueRecord = (QueueRecord) l.get(0); //delete first record in queue
	sessionFactory.getCurrentSession().delete(queueRecord);
	return queueRecord.getMovie(); //return first movie in queue
    }

    @Override
    //this method does not work. 
    public Movie pollInStockMovie(User user) { 
	String querystring = "select q from QueueRecord q join q.movie join q.movie.inventoryItems where UserID = :u and inStock = 1 order by Position asc"; //get position of first instock movie
	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("u", user.getUserID());
	query.setMaxResults(1);
	List<?> l = query.list();
	if(l.isEmpty()) return null;
	QueueRecord q = (QueueRecord) l.get(0);
	int position = q.getPosition();
	sessionFactory.getCurrentSession().delete(q);
	Movie movie = q.getMovie(); //get movie in question
	querystring = "from QueueRecord where UserID = :id and Position > :pos"; //decrement the positions of the movies in later positions
	query = sessionFactory.getCurrentSession().createQuery(querystring);
	query.setParameter("id", user.getUserID());
	query.setParameter("pos", position);
	l = query.list();
	for(Object o : l) {//decrement Positions of queuerecords in our list
	    QueueRecord queueRecord = (QueueRecord) o;
	    queueRecord.setPosition(queueRecord.getPosition() - 1);
	    sessionFactory.getCurrentSession().merge(queueRecord);
	}
	return movie;
    }
    
    @Override
    public List<QueueRecord> getQueueListForUser(User user) {
    	String querystring = "from QueueRecord where UserID = :u order by Position asc";
    	Query query = sessionFactory.getCurrentSession().createQuery(querystring);
    	query.setParameter("u", user.getUserID());
    	List<?> l = query.list();
    	List<QueueRecord> queueList = new ArrayList<QueueRecord>();
    	for (Object o : l) {
    		queueList.add((QueueRecord) o); // objects are cast to queue
    	}
    	return queueList;
    }


}
